/**
 * TranslationsTable.java
 *
 * Copyright (C) 2009,  Tomislav Milkovic
 *
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program. if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
 */
package extjsdyntran.translation.store.schema;


import java.sql.*;
import java.util.ArrayList;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import extjsdyntran.translation.store.db.ATable;
import extjsdyntran.translation.store.db.DBaseFactory;
import extjsdyntran.translation.store.db.IRecord;
import extjsdyntran.translation.store.db.ITable;


public class TranslationsTable extends ATable{

    private static TranslationsTable instance;
    private static final String tableName = "Translations";
    //private PreparedStatement prstGetByRef;
    final private Logger logger = LoggerFactory.getLogger(TranslationsTable.class);

    
    private static final String createTable =
        "Create MEMORY Table " + tableName + " (" +
        "Obj OBJECT, " +                           
        "Id INTEGER IDENTITY PRIMARY KEY, " +      
        "IdLang INTEGER , " +          
        "Text VARCHAR(1024) NOT NULL, " +          
        "RefId INTEGER, " +         
        "UNIQUE (Id, IdLang) " +
        ");";
	private PreparedStatement prstGetByRef;    
    

    public TranslationsTable() {
        instance = this;

        if(getCount() == -1) {
        	DBaseFactory.statementExecute(createTable);            
        	DBaseFactory.statementExecute("ALTER TABLE " + tableName + " ALTER COLUMN Id RESTART WITH 1;");                    
         }
        
        try {
        	Connection con = DBaseFactory.getConnection();
            prstUpdate = con.prepareStatement("UPDATE " + tableName + " SET IdLang = ?, Text = ?, RefId = ?, Obj = ? WHERE Id = ?;");
            prstDelete = con.prepareStatement("DELETE FROM " + tableName + " WHERE Id = ?;");
            prstGetById = con.prepareStatement("SELECT Obj, Id FROM " + tableName + " WHERE Id = ?;");
            prstSelectAll = con.prepareStatement("SELECT Obj, Id, IdLang, Text, RefId FROM " + tableName + " WHERE IdLang = ? ORDER BY Text;");
            prstInsert = con.prepareStatement("INSERT INTO " + tableName + " (Obj, Id, IdLang, Text, RefId) VALUES (?, ?, ?, ?, ?)");
            prstDeleteAll = con.prepareStatement("DELETE FROM " + tableName + ";");  
            //prstGetByValue = con.prepareStatement("SELECT LIMIT 0 1 Obj, Id  FROM " + tableName + " WHERE Text = ? and IdLang = ?;");
            prstGetByValue = con.prepareStatement("SELECT Obj, Id  FROM " + tableName + " WHERE Text = ? and IdLang = ?;");
            prstGetByRef = con.prepareStatement("SELECT Obj, Id FROM " + tableName + " WHERE RefId = ?;");
        }
        catch(SQLException e) {
        	logger.error("Error preparing table {}",tableName, e);
            throw new IllegalStateException("Error preparing table " + tableName + ".");
        }
        
    }

     
    
    public  ArrayList<IRecord> sqlGetAllByLanguage(int langID) {

        try {

        	prstSelectAll.setInt(1, langID);
            return  sqlSelectAll(prstSelectAll);
           
        }
        catch(SQLException e) {
        	logger.error("Error retrieving records!",  e);
        }
        return null;   	   
 	   
    }
    
    
    public  IRecord sqlGetById(int iD) {

        try {

        	prstGetById.setInt(1, iD);
            ArrayList<IRecord> recs = sqlSelectAll(prstGetById);
            if (recs.size() > 0) return recs.get(0);
        }
        catch(SQLException e) {
        	logger.error("Error retrieving record by ID!",  e);
        }
        return null;   	   
 	   
    }

    public  IRecord sqlGetByRefId(int refID) {

        try {
        	prstGetByRef.setInt(1, refID);
            ArrayList<IRecord> recs = sqlSelectAll(prstGetByRef );
            if (recs.size() > 0) return recs.get(0);
            
        }
        catch(SQLException e) {
        	logger.error("Error retrieving record by reference ID!",  e);
        }
        return null;   	   
 	   
    }
    
    public  IRecord sqlGetByValue(String value, int langID) {

        try {

        	prstGetByValue.setString(1, value);
        	prstGetByValue.setInt(2, langID);
            ArrayList<IRecord> recs = sqlSelectAll(prstGetByValue);
            if (recs.size() > 0) return recs.get(0);
            
        }
        catch(SQLException e) {
        	logger.error("Error retrieving record by value!",  e);
        }
        return null;   	   
 	   
    }
    
    
    public boolean sqlInsert(IRecord fn) {
        boolean status = false;
        try {
            prstInsert.setObject(1, fn, Types.JAVA_OBJECT);
            prstInsert.setNull(2, Types.INTEGER);
            prstInsert.setInt(3, ((TranslationsPojo)fn).getLangId());
            prstInsert.setString(4, ((TranslationsPojo)fn).getText());
            prstInsert.setInt(5, ((TranslationsPojo)fn).getRefId());
            
            int nbChanged = DBaseFactory.statementExecute(prstInsert);
            status = nbChanged > 0;
            fn.setId(DBaseFactory.getIdentity());
            logger.info("Statement execute {} rows affected {} status {}", new Object[]{prstInsert , nbChanged ,status});
        }
        catch(SQLException e) {
        	logger.error("Error inserting record!",  e);
        }
        return status;
    }
    
    public boolean sqlUpdate(IRecord fn) {
        boolean status = false;
        try {
        	prstUpdate.setInt(1, ((TranslationsPojo)fn).getLangId());
            prstUpdate.setString(2, ((TranslationsPojo)fn).getText());
            prstUpdate.setInt(3, ((TranslationsPojo)fn).getRefId());
            prstUpdate.setObject(4, fn, Types.JAVA_OBJECT);            
            prstUpdate.setInt(5, fn.getId());
            
            int nbChanged = DBaseFactory.statementExecute(prstUpdate);
            status = nbChanged > 0;
            logger.info("Statement execute {} rows affected {} status {}", new Object[]{prstUpdate , nbChanged ,status});
        }
        catch(SQLException e) {
        	logger.error("Error updating record!",  e);
        }
        return status;
    }
    
    /** Returns the number of entries in the table */
    public int getCount() {
        return getCount(tableName);
    }
 
	public ITable getInstance() {
		return instance;
	}
	
}